﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KSSModel;

namespace KSSOnline.Controller
{
    public class Search
    {
        static List<tblDocSearch> quickresult = new List<tblDocSearch>();
        public static List<tblDocSearch> GetQuickSearchQuery(string searchCiteria)
        {
            quickresult = new List<tblDocSearch>();
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var searchQuery = from d in dataContext.tblDocs
                                      join dp in dataContext.docPropertyDatas
                                          on d.id equals dp.docId
                                      select new
                                      {
                                          createdByName = d.createdByName,
                                          createdBy = d.createdBy,
                                          departmentName = d.departmentName,
                                          title = d.title,
                                          cash = d.cash,
                                          createdDate = d.createdDate,
                                          status = d.status,
                                          score = d.score,
                                          id = d.id,
                                          propertValue = dp.dataNvarchar,
                                          propertyName = dp.propertyName
                                      };

                    var searchQueryTitle = searchQuery.Where(d => d.title.Contains(searchCiteria)).Select(
                            s =>
                            new tblDocSearch
                            {
                                id = s.id,
                                createdByName = s.createdByName,
                                createdBy = s.createdBy,
                                departmentName = s.departmentName,
                                title = s.title,
                                cash = s.cash,
                                createdDate = s.createdDate,
                                status = s.status,
                                score = s.score,
                                CostSaving = s.propertValue
                            }).ToList();
                    SumSearch(searchQueryTitle);

                    var searchQueryCreateByName = searchQuery.Where(d => d.createdByName.Contains(searchCiteria)).Select(
                            s =>
                            new tblDocSearch
                            {
                                id = s.id,
                                createdByName = s.createdByName,
                                createdBy = s.createdBy,
                                departmentName = s.departmentName,
                                title = s.title,
                                cash = s.cash,
                                createdDate = s.createdDate,
                                status = s.status,
                                score = s.score,
                                CostSaving = s.propertValue
                            }).ToList();
                    SumSearch(searchQueryCreateByName);

                    var searchQueryDepartmentName = searchQuery.Where(d => d.departmentName.Contains(searchCiteria)).Select(
                           s =>
                           new tblDocSearch
                           {
                               id = s.id,
                               createdByName = s.createdByName,
                               createdBy = s.createdBy,
                               departmentName = s.departmentName,
                               title = s.title,
                               cash = s.cash,
                               createdDate = s.createdDate,
                               status = s.status,
                               score = s.score,
                               CostSaving = s.propertValue
                           }).ToList();
                    SumSearch(searchQueryDepartmentName);

                    var searchQueryPropertyName = searchQuery.Where(d => d.propertyName.Contains(searchCiteria)).Select(
                            s =>
                            new tblDocSearch
                            {
                                id = s.id,
                                createdByName = s.createdByName,
                                createdBy = s.createdBy,
                                departmentName = s.departmentName,
                                title = s.title,
                                cash = s.cash,
                                createdDate = s.createdDate,
                                status = s.status,
                                score = s.score,
                                CostSaving = s.propertValue
                            }).ToList();
                    SumSearch(searchQueryPropertyName);

                    var searchQueryPropertyValue = searchQuery.Where(d => d.propertValue.Contains(searchCiteria)).Select(
                            s =>
                            new tblDocSearch
                            {
                                id = s.id,
                                createdByName = s.createdByName,
                                createdBy = s.createdBy,
                                departmentName = s.departmentName,
                                title = s.title,
                                cash = s.cash,
                                createdDate = s.createdDate,
                                status = s.status,
                                score = s.score,
                                CostSaving = s.propertValue
                            }).ToList();
                    SumSearch(searchQueryPropertyValue);


                    return quickresult;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }

        private static void SumSearch(List<tblDocSearch> search)
        {

            foreach (var ss in search)
            {
                if (!quickresult.Any(s => s.id == ss.id))
                {
                    quickresult.Add(ss);
                }
            }
        }

        public static List<tblDocSearch> GetSearchQuery(SearchCiteria searchCiteria)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var allhr = from doc in dataContext.tblEDMSHRs
                                select doc;

                    if (!string.IsNullOrEmpty(searchCiteria.Location) && searchCiteria.Location.ToLower() != "all")
                    {
                        allhr = allhr.Where(hr => hr.mlocation == searchCiteria.Location.ToLower());
                    }
                    bool hasCiteria = false;
                    var searchQuery = from d in dataContext.tblDocs
                                      join hr in allhr on d.createdBy equals hr.mstaffno
                                      join dp in dataContext.docPropertyDatas
                                          on d.id equals dp.docId
                                      where dp.propertyName == "savecost"
                                      select new
                                      {
                                          createdByName = d.createdByName,
                                          createdBy = d.createdBy,
                                          departmentName = d.departmentName,
                                          title = d.title,
                                          cash = d.cash,
                                          createdDate = d.createdDate,
                                          status = d.status,
                                          score = d.score,
                                          id = d.id,
                                          CostSaving = dp.dataNvarchar,
                                      };
                    if (!string.IsNullOrEmpty(searchCiteria.CreatedByName))
                    {
                        hasCiteria = true;
                        searchQuery = searchQuery.Where(d => d.createdByName.Contains(searchCiteria.CreatedByName));
                    }
                    if (!string.IsNullOrEmpty(searchCiteria.CreatedBy))
                    {
                        hasCiteria = true;
                        searchQuery = searchQuery.Where(d => d.createdBy.Contains(searchCiteria.CreatedBy));
                    }
                    if (!string.IsNullOrEmpty(searchCiteria.DepartmentName))
                    {
                        hasCiteria = true;
                        searchQuery = searchQuery.Where(d => d.departmentName.Contains(searchCiteria.DepartmentName));
                    }
                    if (!string.IsNullOrEmpty(searchCiteria.Title))
                    {
                        hasCiteria = true;
                        searchQuery = searchQuery.Where(d => d.title.Contains(searchCiteria.Title));
                    }
                    if (!string.IsNullOrEmpty(searchCiteria.Cash))
                    {
                        hasCiteria = true;
                        decimal? cash = Convert.ToDecimal(searchCiteria.Cash);
                        searchQuery = searchQuery.Where(d => d.cash == cash);
                    }

                    if (searchCiteria.Datefrom.HasValue && searchCiteria.Datefrom.Value > DateTime.MinValue)
                    {
                        hasCiteria = true;
                        searchQuery = searchQuery.Where(p => p.createdDate >= searchCiteria.Datefrom);
                    }

                    if (searchCiteria.Dateto.HasValue && searchCiteria.Dateto.Value > DateTime.MinValue)
                    {
                        hasCiteria = true;
                        searchQuery = searchQuery.Where(p => p.createdDate < searchCiteria.Dateto);
                    }

                   
                    var result =
                        searchQuery.Select(
                            s =>
                            new tblDocSearch
                                {
                                    id = s.id,
                                    createdByName = s.createdByName,
                                    createdBy = s.createdBy,
                                    departmentName = s.departmentName,
                                    title = s.title,
                                    cash = s.cash,
                                    createdDate = s.createdDate,
                                    status = s.status,
                                    score = s.score,
                                    CostSaving = s.CostSaving
                                }).ToList();
                    if (!hasCiteria)
                    {
                        result = new List<tblDocSearch>();
                    }

                    if (searchCiteria.cb12 > 0)
                    {
                        var getSubItem12 = (from at in dataContext.tblA1Items
                                            where at.a1CateId == searchCiteria.cb12
                                            select at.a1ItemId.ToString()).ToArray();
                        var resultcate = (from d in dataContext.tblDocs
                                          join hr in allhr on d.createdBy equals hr.mstaffno
                                          join dp in dataContext.docPropertyDatas
                                              on d.id equals dp.docId
                                          where getSubItem12.Contains(dp.propertyName) && dp.dataNvarchar.ToString().ToLower() == "true"
                                          select new tblDocSearch
                                          {
                                              id = d.id,
                                              createdByName = d.createdByName,
                                              createdBy = d.createdBy,
                                              departmentName = d.departmentName,
                                              title = d.title,
                                              cash = d.cash,
                                              createdDate = d.createdDate,
                                              status = d.status,
                                              score = d.score,
                                              CostSaving = ""
                                          }).Distinct().ToList();

                        if (resultcate.Count() > 0)
                        {
                            foreach (var item in resultcate)
                            {
                                if (!result.Any(k => k.id == item.id))
                                {
                                    result.Add(item);
                                }
                            }
                        }
                    }

                    if (searchCiteria.cb13 > 0)
                    {
                        var getSubItem13 = (from at in dataContext.tblA1Items
                                            where at.a1CateId == searchCiteria.cb13
                                            select at.a1ItemId.ToString()).ToArray();
                        var resultcate = (from d in dataContext.tblDocs
                                          join hr in allhr on d.createdBy equals hr.mstaffno
                                          join dp in dataContext.docPropertyDatas
                                              on d.id equals dp.docId
                                          where getSubItem13.Contains(dp.propertyName) && dp.dataNvarchar.ToString().ToLower() == "true"
                                          select new tblDocSearch
                                          {
                                              id = d.id,
                                              createdByName = d.createdByName,
                                              createdBy = d.createdBy,
                                              departmentName = d.departmentName,
                                              title = d.title,
                                              cash = d.cash,
                                              createdDate = d.createdDate,
                                              status = d.status,
                                              score = d.score,
                                              CostSaving = ""
                                          }).Distinct().ToList();
                        if (resultcate.Count() > 0)
                        {
                            foreach (var item in resultcate)
                            {
                                if (!result.Any(k => k.id == item.id))
                                {
                                    result.Add(item);
                                }
                            }
                        }
                    }
                    if (searchCiteria.cb14 > 0)
                    {
                        var getSubItem13 = (from at in dataContext.tblA1Items
                                            where at.a1CateId == searchCiteria.cb14
                                            select at.a1ItemId.ToString()).ToArray();
                        var resultcate = (from d in dataContext.tblDocs
                                          join hr in allhr on d.createdBy equals hr.mstaffno
                                          join dp in dataContext.docPropertyDatas
                                              on d.id equals dp.docId
                                          where getSubItem13.Contains(dp.propertyName) && dp.dataNvarchar.ToString().ToLower() == "true"
                                          select new tblDocSearch
                                          {
                                              id = d.id,
                                              createdByName = d.createdByName,
                                              createdBy = d.createdBy,
                                              departmentName = d.departmentName,
                                              title = d.title,
                                              cash = d.cash,
                                              createdDate = d.createdDate,
                                              status = d.status,
                                              score = d.score,
                                              CostSaving = ""
                                          }).Distinct().ToList();
                        if (resultcate.Count() > 0)
                        {
                            foreach (var item in resultcate)
                            {
                                if (!result.Any(k => k.id == item.id))
                                {
                                    result.Add(item);
                                }
                            }
                        }
                    }
                    if (searchCiteria.cb16 > 0)
                    {
                        var getSubItem13 = (from at in dataContext.tblA1Items
                                            where at.a1CateId == searchCiteria.cb16
                                            select at.a1ItemId.ToString()).ToArray();
                        var resultcate = (from d in dataContext.tblDocs
                                          join hr in allhr on d.createdBy equals hr.mstaffno
                                          join dp in dataContext.docPropertyDatas
                                              on d.id equals dp.docId
                                          where getSubItem13.Contains(dp.propertyName) && dp.dataNvarchar.ToString().ToLower() == "true"
                                          select new tblDocSearch
                                          {
                                              id = d.id,
                                              createdByName = d.createdByName,
                                              createdBy = d.createdBy,
                                              departmentName = d.departmentName,
                                              title = d.title,
                                              cash = d.cash,
                                              createdDate = d.createdDate,
                                              status = d.status,
                                              score = d.score,
                                              CostSaving = ""
                                          }).Distinct().ToList();
                        if (resultcate.Count() > 0)
                        {
                            foreach (var item in resultcate)
                            {
                                if (!result.Any(k => k.id == item.id))
                                {
                                    result.Add(item);
                                }
                            }
                        }
                    }

                    return result.Distinct().ToList();
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }

        }
    }
    public class SearchCiteria
    {
        public string CreatedByName { get; set; }
        public string CreatedBy { get; set; }
        public string DepartmentName { get; set; }
        public string DivName { get; set; }
        public string Title { get; set; }
        public string Cash { get; set; }
        public DateTime? Datefrom { get; set; }
        public DateTime? Dateto { get; set; }
        public string Location { get; set; }
        public int cb12 { get; set; }
        public int cb13 { get; set; }
        public int cb14 { get; set; }
        public int cb16 { get; set; }
    }
}
